package com.peiwangroup.orderservice.repository;

import com.peiwangroup.orderservice.dto.OrderDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public interface OrderRepository extends JpaRepository<OrderDTO, Long> {
    
    @Query("SELECT o FROM OrderDTO o WHERE DATE(o.createTime) = DATE(:date)")
    List<OrderDTO> findByDate(Date date);
    
    @Query("SELECT o FROM OrderDTO o WHERE YEAR(o.createTime) = :year AND MONTH(o.createTime) = :month")
    List<OrderDTO> findByYearAndMonth(@Param("year") int year, @Param("month") int month);
    
    @Query("SELECT o FROM OrderDTO o WHERE o.createTime BETWEEN :startDate AND :endDate")
    List<OrderDTO> findByDateBetween(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
    
    @Query("SELECT o.companion as name, SUM(o.finalAmount) as amount " +
           "FROM OrderDTO o WHERE YEAR(o.createTime) = :year AND MONTH(o.createTime) = :month " +
           "GROUP BY o.companion ORDER BY amount DESC")
    List<Object[]> getMonthlyRanking(int year, int month);
    
    @Query("SELECT o FROM OrderDTO o WHERE DATE(o.createTime) = DATE(:date) AND (o.companion = :username OR o.customer = :username OR o.dispatch = :username OR o.receptionist = :username)")
    List<OrderDTO> findByDateAndUsername(@Param("date") Date date, @Param("username") String username);
    
    @Query("SELECT o FROM OrderDTO o WHERE YEAR(o.createTime) = :year AND MONTH(o.createTime) = :month AND (o.companion = :username OR o.customer = :username OR o.dispatch = :username OR o.receptionist = :username)")
    List<OrderDTO> findByYearAndMonthAndUsername(@Param("year") int year, @Param("month") int month, @Param("username") String username);
    
    @Query("SELECT o FROM OrderDTO o WHERE o.createTime BETWEEN :startDate AND :endDate AND (o.companion = :username OR o.customer = :username OR o.dispatch = :username OR o.receptionist = :username)")
    List<OrderDTO> findByDateBetweenAndUsername(@Param("startDate") Date startDate, @Param("endDate") Date endDate, @Param("username") String username);
}